#!C:/Perl/bin/perl.exe -w
# Allow user to select an ALEKS student mastery report. Load the report.

use CGI;
use DBI;
use strict;

# Send stderr to stdout for debugging.
*STDERR = *STDOUT;

open DEBUG, ">", "debug.txt" or die "Can't open debug log: $!";

my $styleBlock=<<EOT;
td { padding: 10px }
label { background-color: gray; color: white; padding-left: 2px }
EOT

my $javascript=<<EOT;
function studentSelect() {
    alert('You selected a student!');
}
EOT


# Get the HTTP request, $q, and start a response.
my $q = CGI->new;
print $q->header, $q->start_html(-title=>'ALEKS student reports by MA standards - from repository', -style=>{'-code'=>$styleBlock}, -script=>$javascript );

# Open the repository (SQLite database).
my $dbfile = 'aleks.sqlite';  # in the current directory
my $db = DBI->connect("dbi:SQLite:dbname=$dbfile","","") or die $DBI::errstr;
my $sql;  # a temporary SQL text holder
my $stmt;  # a temporary SQL statement holder
my @row;  # a temporary fetched row holder

# Fetch teacher names.
my @teacher;
$sql = 'select name from teacher order by name;';
$stmt = $db->prepare($sql) or die $db->errstr;
$stmt->execute() or die $stmt->errstr;
while (@row = $stmt->fetchrow_array) {
    push @teacher, @row;
}

# Fetch course names.
my @course;
$sql = 'select name from course order by name;';
$stmt = $db->prepare($sql) or die $db->errstr;
$stmt->execute() or die $stmt->errstr;
while (@row = $stmt->fetchrow_array) {
    push @teacher, @row;
}

# Retrieve student report/batch names.
my @report;  # list of report/batch names
$sql = 'select batch from report order by batch;';
$stmt = $db->prepare($sql) or die $db->errstr;
$stmt->execute() or die $stmt->errstr;
while (@row = $stmt->fetchrow_array) {
    push @report, @row;
}

# Create a form to select student reports.
print $q->start_form(-method=>'GET', -action=>'view student report.pl');
print
    $q->table(
        $q->Tr( 
            $q->td($q->scrolling_list(-name=>'report', -values=>\@report, -size=>30)) 
        ),
        $q->Tr( $q->td($q->submit) )
    );
print $q->end_form;

print $q->end_html;

# Disconnect from the database
$db->disconnect;

close DEBUG;
